package com.itgone.manager.util;

import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.Objects;

/**
 * @Author: earlman
 * @Description:
 * @Date:Create：in 2021-09-24 22:36
 */
public class ExcelUtil {

    public static Workbook openWorkbook(InputStream in) throws IOException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }

        return WorkbookFactory.create(in);
//        if (POIFSFileSystem.hasPOIFSHeader(in)) {
//            return new HSSFWorkbook(in);
//        }
//        if (POIXMLDocument.hasOOXMLHeader(in)) {
//            return new XSSFWorkbook(in);
//        }
//        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }

    public static String getCellValue(Cell cell) {
        if (Objects.isNull(cell)) {
            return null;
        }

        CellType cellType = cell.getCellType();
        if (CellType.FORMULA == cellType) {
            cellType = cell.getCachedFormulaResultType();
        }
        String cellValue;
        switch (cellType) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dateCellValue = cell.getDateCellValue();
                    LocalDateTime localDateTimeCellValue = cell.getLocalDateTimeCellValue();
                    System.out.println(dateCellValue.toString());
                    System.out.println(localDateTimeCellValue.toString());
                    cellValue = localDateTimeCellValue.toString();
                } else {
                    cellValue = String.valueOf((int) cell.getNumericCellValue());
                }
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BLANK:
                cellValue = "";
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                throw new IllegalArgumentException("你的excel单元格类型目前系统不支持");
        }
        return cellValue;
    }


    /**
     * Excel column index begin 1
     *
     * @param colStr
     * @param length
     * @return
     */
    public static int excelColStrToNum(String colStr, int length) {
        int num = 0;
        int result = 0;
        for (int i = 0; i < length; i++) {
            char ch = colStr.charAt(length - i - 1);
            num = (int) (ch - 'A' + 1);
            num *= Math.pow(26, i);
            result += num;
        }
        return result;
    }

    /**
     * Excel column index begin 1
     *
     * @param columnIndex
     * @return
     */
    public static String excelColIndexToStr(int columnIndex) {
        if (columnIndex <= 0) {
            return null;
        }
        String columnStr = "";
        columnIndex--;
        do {
            if (columnStr.length() > 0) {
                columnIndex--;
            }
            columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
            columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
        } while (columnIndex > 0);
        return columnStr;
    }

}
